<?php
namespace other\facebook;
// {"fcblid": "IwAR0I1faniYggwLgrFbKOhYwwHzDqAtGoe-CqLfDe2R9BQ8kmsxGvaakPW0A", "set_id": ["120204960799590189", "120204960799590189"], "camp_id": ["120204960799310189", "120204960799310189"], "ad_id_120204960799870189": null, "ad_id_120204960799870189IwAR0I1faniYggwLgrFbKOhYwwHzDqAtGoe-CqLfDe2R9BQ8kmsxGvaakPW0AIwAR0I1faniYggwLgrFbKOhYwwHzDqAtGoe-CqLfDe2R9BQ8kmsxGvaakPW0A": null}
use other\ShopingConn;
use other\Table;
use think\db\exception\DbException;
use think\Exception;
use think\exception\PDOException;

//  /www/server/php/74/bin/php /www/wwwroot/demo.one/think other ad_facebook
class AnalyzeDataFacebook
{

    /**
     * 更新近几天的数据
     * @var int
     */
    private $dayNum = 20;

    // 类型：0=ad_id,1=campaign_id,2=placement,3=source
    private $k_types = [
        '0' => 'ad_id',
        '1' => 'campaign_id',
        '2' => 'placement',
        '3' => 'source',
    ];

    private $ip_events = [
        1 => 'independent_visitors',
        2 => 'lp_views',
    ];

    /**
     * 处理
     * @throws PDOException
     * @throws DbException
     * @throws Exception
     */
    public function do(){
        $this->ipLog();
        $this->facebookLogs();
        $this->_heData();
    }

    /**
     * 合成数据
     * @return void
     * @throws DbException
     * @throws Exception
     * @throws \think\exception\PDOException
     */
    private function _heData()
    {

        // todo 独立访问量  和  lp访问量    1706810014989573
        foreach ($this->k_types as $k_type => $field) {
            foreach ($this->ip_events as $event_type => $num_field) {
                $data = ShopingConn::getConn()->query($this->ipLogSql($field, $event_type));
                if ($data) {
                    foreach ($data as $row) {
                        $needRow = [
                            'date' => $row['usadate'],
                            'k_val' => $row[$field],
                            'mall_type' => $row['mall_type'],
                            'k_type' => $k_type,
                            $num_field => $row['num'],
                        ];
                        $this->updateRow($needRow);
                    }
                }
            }
        }


        // todo 成交订单量
        $num_field = 'completed_orders';
        foreach ($this->k_types as $k_type => $field){
            $data = ShopingConn::getConn()->query($this->orderLogSql($field));
            if ($data){
                foreach ($data as $row){
                    $needRow = [
                        'date' => $row['usadate'],
                        'k_val' => $row[$field],
                        'mall_type' => $row['mall_type'],
                        'k_type' => $k_type,
                        $num_field => $row['num'],
                    ];
                    $this->updateRow($needRow);
                }
            }
        }

        // todo (去重后的)弃单量
        $num_field = 'abandoned_orders';
        foreach ($this->k_types as $k_type => $field){
            $data = ShopingConn::getConn()->query($this->orderLogSql($field,'b.`is_abandoning`=1'));
            if ($data){
                foreach ($data as $row){
                    $needRow = [
                        'date' => $row['usadate'],
                        'k_val' => $row[$field],
                        'mall_type' => $row['mall_type'],
                        'k_type' => $k_type,
                        $num_field => $row['num'],
                    ];
                    $this->updateRow($needRow);
                }
            }
        }
    }

    /**
     * 更新单行的数据
     * @param $row
     * @return void
     * @throws Exception
     * @throws DbException
     * @throws \think\exception\PDOException
     */
    private function updateRow($row){
        if (isset($row['date']) && $row['date']){
            if (isset($row['k_val']) && $row['k_val']){
                if (isset($row['mall_type']) && $row['mall_type']){
                    $value = ShopingConn::getConn()->table(Table::Table_analyze_data_facebook)
                        ->where('date', $row['date'])
                        ->where('k_val', $row['k_val'])
                        ->where('mall_type', $row['mall_type'])
                        ->value('id');

                    if (!$value){
                        ShopingConn::getConn()->table(Table::Table_analyze_data_facebook)->insert($row);
                    }else{
                        ShopingConn::getConn()->table(Table::Table_analyze_data_facebook)
                            ->where('id',$value)->update($row);
                    }

                }
            }
        }
    }

    // 最近七天  每次只跑最近七天  todo  美国时间；ip_log 存储的就是美国时间
    private function ipLogSql($field,$event_type=1): string
    {
        $time = $this->_getRefreshTime();
        return "select count(`id`) as num,mall_type,".$field.",usadate from (SELECT mall_type,id,DATE_FORMAT(ip_log.created_at, '%Y-%m-%d') AS usadate,".$field." from ip_log where is_run_facebook='1' and  event_type=".$event_type." and created_at > ".$time." ) as a group by ".$field.",usadate,mall_type order by usadate desc";
    }

    private function orderLogSql($field,$b_where = 'b.`status`=1 and b.order_type=1'){
        $time = $this->_getRefreshTime();
        $sql = "select count(`id`) as num,mall_type,".$field.",usadate from
(select a.id,a.order_id,a.".$field.",b.mall_type,b.`status`,DATE_FORMAT(FROM_UNIXTIME(b.created_at-57600), '%Y-%m-%d') AS usadate from  order_facebook_logs as a LEFT JOIN orders as b on a.order_id=b.id where a.is_run_facebook = '1' and ".$b_where." and a.order_id > 0 and b.created_at>".$time." group by a.id)
as c group by ".$field.",usadate,mall_type order by usadate desc";

        return $sql;
    }


    private function _getRefreshTime()
    {
        return time() - $this->dayNum * 3600 * 24;
    }


    /**
     * ip_log 访问的 facebook 记录处理
     * @return void
     */
    function ipLog()
    {
        $table = Table::Table_Ip_Log;
        ShopingConn::getConn()->table($table)->where('is_run_facebook','0')
            ->field('id,params')
            ->chunk(10,function ($data)use($table){
                if ($data){
                    foreach ($data as $row)
                    {
                        $this->_uRow($row,$table);
                    }
                }

            });
    }




    /**
     * 订单表的facebook 记录处理
     * @return void
     * @throws Exception
     */
    function facebookLogs()
    {
        $table = Table::Table_order_facebook;
        ShopingConn::getConn()->table($table)->where('is_run_facebook','0')
            ->field('id,params')
            ->chunk(10,function ($data)use($table){
                if ($data){
                    foreach ($data as $row)
                    {
                        $this->_uRow($row,$table);
                    }
                }

            });
    }


    /**
     * 回填 原有日志的单行数据
     * @param $row
     * @param $table
     * @return void
     * @throws Exception
     * @throws DbException
     * @throws \think\exception\PDOException
     */
    private function _uRow($row,$table)
    {
        $uRow = [];
        $is_run_facebook = '2';
        if (isset($row['params']) && $row['params'])
        {
            $params = json_decode($row['params'],true);
            if (isset($params['ad_id']) && $params['ad_id'] && !is_array($params['ad_id'])){
                $is_run_facebook = '1';
                $uRow['ad_id'] = $params['ad_id'];

                $uRow['campaign_id'] = $params['campaign_id'] ?? 0;
                $uRow['source'] = $params['source'] ?? '';
                $uRow['placement'] = $params['placement'] ?? '';
            }
        }

        $uRow['is_run_facebook'] = $is_run_facebook;
        ShopingConn::getConn()->table($table)->where('id',$row['id'])->update($uRow);
    }
}
